package com.example.mapper;

import java.util.Date;
import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.domain.Business;

@Repository // 代表持久层
// 在对应的Mapper上面继承基本的类 BaseMapper
public interface BusinessMpper extends BaseMapper<Business>{
	  /**
     * 查询所有
     * @return
     */
    @Select("select 人力.LASTNAME, 部门.DEPARTMENTNAME, 出差清算.ksrq as 开始日期, 出差清算.jsrq as 结束日期 ,sum(出差清算.tsqs ) as 天数清算, sum(出差清算.jtfqs) as 交通费清算, sum(出差清算.zdfqs)  as 招待费清算, sum(出差清算.hsbzqs)  as 伙食补助清算,  sum(出差清算.zsfqs)  as 住宿费清算, sum(出差清算.hjqs)  as 合计清算 from formtable_main_7 as 出差清算 left outer join hrmresource as 人力 on 出差清算.ygmc = 人力.ID left outer join hrmdepartment as 部门 on 出差清算.REQDEPT = 部门.id where (reqdept  = 3\r\n"
    		+ "or reqdept = 106\r\n"
    		+ "or reqdept = 105\r\n"
    		+ "or reqdept = 62\r\n"
    		+ "or reqdept = 63\r\n"
    		+ "or reqdept = 64\r\n"
    		+ "or reqdept = 65\r\n"
    		+ "or reqdept = 66\r\n"
    		+ "or reqdept = 5\r\n"
    		+ "or reqdept = 7\r\n"
    		+ "or reqdept = 4\r\n"
    		+ "or reqdept = 12\r\n"
    		+ "or reqdept = 61\r\n"
    		+ "or reqdept = 69\r\n"
    		+ "or reqdept = 8\r\n"
    		+ "or reqdept = 9\r\n"
    		+ "or reqdept = 6\r\n"
    		+ "or reqdept = 34)\r\n"
    		+ "and 出差清算.ksrq >= #{startData}\r\n"
    		+ "and 出差清算.jsrq <= #{endData}\r\n"
    		+ "group by 人力.LASTNAME,部门.DEPARTMENTNAME order by 合计清算 desc , 出差清算.id  desc  limit 10")
    @Results({
    	@Result(property = "tsqs",column = "天数清算"),
    	@Result(property = "jtfqs",column = "交通费清算"),
    	@Result(property = "zdfqs",column = "招待费清算"),
    	@Result(property = "hsbzqs",column = "伙食补助清算"),
    	@Result(property = "zsfqs",column = "住宿费清算"),
    	@Result(property = "hjqs",column = "合计清算"),
    	})
    List<Business> findAll(String startData, String endData);
}
